﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for AwardIssuedDAO
/// </summary>
public class AwardIssuedDAO : DBAccess, IAwardIssued
{
    #region sql commands
    private string SQL_INSERT_AWARDSISSUED = "insert into AwardsIssued values(@awardID, @postingID)";
    private string SQL_UPDATE_AWARDSISSUED = "update AwardsIssued set PostingID = @postingID where AwardID = @awardID";
    private string SQL_DELETE_AWARDSISSUED = "delete from AwardsIssued where AwardID = @awardID and PostingID = @postingID";
    private string SQL_AWARD_ISSUED = "select c.CompetitionID, c.CompetitionName, a.AwardID, a.AwardName, a.Prize, ui.Fullname, p.PostingName, p.PostingID  from Competitions as c "
        + "join Awards as a on c.CompetitionID = a.CompetitionID join AwardsIssued as ai on a.AwardID = ai.AwardID join Postings as p on ai.PostingID = p.PostingID "
        + "join UserInfomations as ui on p.StudentID = ui.UserID where c.CompetitionID = @competitionID";

    private string SQL_GET_AWARD_ISSUED = "select a.AwardName, a.Prize, ui.Fullname, p.PostingName, ui.Class, ui.Phone  from Competitions as c join Awards as a on c.CompetitionID = a.CompetitionID join AwardsIssued as ai on a.AwardID = ai.AwardID join Postings as p on ai.PostingID = p.PostingID join UserInfomations as ui on p.StudentID = ui.UserID where c.CompetitionID = @competitionID";
    private string SQL_GET_STUDENT_PRICE_AWARDED = "select a.AwardName, a.Prize, ui.Fullname, c.CompetitionID,p.PostingID  from Competitions as c join Awards as a on c.CompetitionID = a.CompetitionID join AwardsIssued as ai on a.AwardID = ai.AwardID join Postings as p on ai.PostingID = p.PostingID join UserInfomations as ui on p.StudentID = ui.UserID where c.CompetitionID = @competitionID";
    

    private string SQL_GET_ALL = "select PostingID from AwardsIssued  where AwardID = @awardID";
    string message;
    #endregion


    #region AwardIssued members
    public AwardIssuedDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    /// <summary>
    /// Get PostingID 
    /// </summary>
    /// <returns>int64</returns>
    public Int64 GetPostID(int awardID)
    {
        Int64 postID = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GET_ALL, conn);
            cmd.Parameters.AddWithValue("@awardID", awardID);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    postID = reader.GetInt64(0); 
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
       
        return postID;
    }
    /// <summary>
    /// Insert Award
    /// </summary>
    /// <returns>int</returns>
    public int Insert(AwardIssuedInfo aii)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT_AWARDSISSUED, conn);
            cmd.Parameters.AddWithValue("@awardID", aii.AwardId);
            cmd.Parameters.AddWithValue("@postingID", aii.PostingId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Update award issued for student
    /// </summary>
    /// <param name="aii"></param>
    /// <returns>int</returns>
    public int Update(AwardIssuedInfo aii)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_AWARDSISSUED, conn);
            cmd.Parameters.AddWithValue("@awardID", aii.AwardId);
            cmd.Parameters.AddWithValue("@postingID", aii.PostingId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    /// Delete award issued for student
    /// </summary>
    /// <param name="postingId"></param>
    /// <returns>int</returns>
    public int Delete(AwardIssuedInfo award)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_AWARDSISSUED, conn);
            cmd.Parameters.AddWithValue("@awardID", award.AwardId);
            cmd.Parameters.AddWithValue("@postingID", award.PostingId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;

    }
    /// <summary>
    /// Get Award Issued by CompetitionID
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet Award_Issued(int competitionID)
    {
        DataSet ds = new DataSet();
        try
        {   
            SqlDataAdapter adapter = new SqlDataAdapter(SQL_AWARD_ISSUED, conn);
            adapter.SelectCommand.Parameters.AddWithValue("@competitionID", competitionID);
            adapter.Fill(ds);
        }
        catch (SqlException se)
        {
            message = se.Message;
        }
        catch (Exception e)
        {
            message = e.Message;
        }
        return ds;
    }
    /// <summary>
    /// Get Award Issued by CompetitionID
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet Get_Award_Issued(int competitionID)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(SQL_GET_AWARD_ISSUED, conn);
            adapter.SelectCommand.Parameters.AddWithValue("@competitionID", competitionID);
            adapter.Fill(ds);
        }
        catch (SqlException se)
        {
            message = se.Message;
        }
        catch (Exception e)
        {
            message = e.Message;
        }
        return ds;
    }

    /// <summary>
    /// Get Get Student Awarded
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet GetStudent_nearestAward()
    {
        DataSet ds = new DataSet();
        try
        {
            
            CompetitionDAO comDAO = new CompetitionDAO();
            int competitionID = comDAO.Get_Nearest_PreviousCompetition();
            if (competitionID != -1)
            {
                SqlDataAdapter adapter = new SqlDataAdapter(SQL_GET_STUDENT_PRICE_AWARDED, conn);
                adapter.SelectCommand.Parameters.AddWithValue("@competitionID", competitionID);
                adapter.Fill(ds);
            }
           
        }
        catch (SqlException se)
        {
            message = se.Message;
        }
        catch (Exception e)
        {
            message = e.Message;
        }
        return ds;
    }

    public DataSet Award_Issued()
    {
        throw new NotImplementedException();
    }
    #endregion

}